﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace BLL
{
    /// <summary>
    /// 资金详情
    /// </summary>
    public class QS_PLAN_DETAILS_BLL : BaseBLL
    {
        #region 基础

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Insert(QS_PLAN_DETAILS entity)
        {
            var seq = GetSequence("SEQ_PLAN_DETAILS");
            entity.N_ID = seq;
            var sql = @"INSERT INTO QS_PLAN_DETAILS (
	N_ID,
	N_PRO_ID,
	C_VALUE1,
	C_VALUE2,
	C_VALUE3,
	C_VALUE4,
	C_VALUE5,
	C_VALUE6,
	C_VALUE7,
	N_STATE,
	N_PLAN_ID,
	C_VALUE8,
	C_VALUE9,
	C_VALUE10,
    C_VALUE11
)
VALUES
	(
		{0},
        {1},
        '{2}',
        '{3}',
        '{4}',
        '{5}',
        '{6}',
        '{7}',
        '{8}',
        {9},
        {10},
        '{11}',
        '{12}',
        '{13}',
        '{14}'
	)";
            sql = string.Format(sql,
                entity.N_ID,
                entity.N_PRO_ID,
                entity.C_VALUE1 == null ? "" : entity.C_VALUE1,
                entity.C_VALUE2 == null ? "" : entity.C_VALUE2,
                entity.C_VALUE3 == null ? "" : entity.C_VALUE3,
                entity.C_VALUE4 == null ? "" : entity.C_VALUE4,
                entity.C_VALUE5 == null ? "" : entity.C_VALUE5,
                entity.C_VALUE6 == null ? "" : entity.C_VALUE6,
                entity.C_VALUE7 == null ? "" : entity.C_VALUE7,
                entity.N_STATE,
                entity.N_PLAN_ID,
                entity.C_VALUE8 == null ? "" : entity.C_VALUE8,
                entity.C_VALUE9 == null ? "" : entity.C_VALUE9,
                entity.C_VALUE10 == null ? "" : entity.C_VALUE10,
                entity.C_VALUE11 == null ? "" : entity.C_VALUE11);
            if (NoQuery(sql) == 0)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool Update(QS_PLAN_DETAILS entity)
        {
            var seq = GetSequence("SEQ_PLAN_DETAILS");
            entity.N_ID = seq;
            var sql = @"UPDATE QS_PLAN_DETAILS
SET
	N_PRO_ID = {1},
	C_VALUE1 = '{2}',
	C_VALUE2 = '{3}',
	C_VALUE3 = '{4}',
	C_VALUE4 = '{5}',
	C_VALUE5 = '{6}',
	C_VALUE6 = '{7}',
	C_VALUE7 = '{8}',
	N_STATE = {9},
	N_PLAN_ID = {10},
	C_VALUE8 = '{11}',
	C_VALUE9 = '{12}',
	C_VALUE10 = '{13}',
	C_VALUE11 = '{14}'
WHERE
	N_ID = {0}";
            sql = string.Format(sql, entity.N_ID, entity.N_PRO_ID, entity.C_VALUE1 == null ? "" : entity.C_VALUE1, entity.C_VALUE2 == null ? "" : entity.C_VALUE2, entity.C_VALUE3 == null ? "" : entity.C_VALUE3, entity.C_VALUE4 == null ? "" : entity.C_VALUE4, entity.C_VALUE5 == null ? "" : entity.C_VALUE5, entity.C_VALUE6 == null ? "" : entity.C_VALUE6, entity.C_VALUE7 == null ? "" : entity.C_VALUE7, entity.N_STATE, entity.N_PLAN_ID, entity.C_VALUE8 == null ? "" : entity.C_VALUE8, entity.C_VALUE9 == null ? "" : entity.C_VALUE9, entity.C_VALUE10 == null ? "" : entity.C_VALUE10, entity.C_VALUE11 == null ? "" : entity.C_VALUE11);
            if (NoQuery(sql) == 0)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(decimal id)
        {
            var sql = "DELETE QS_PLAN_DETAILS WHERE N_ID=" + id;
            if (NoQuery(sql) == 0)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="applyId"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public bool DeleteByApplyId(decimal applyId, decimal proId, int state, string code)
        {
            var sql = "DELETE QS_PLAN_DETAILS WHERE N_PLAN_ID=" + applyId + " AND N_PRO_ID=" + proId;
            sql += " AND N_STATE=" + state;
            sql += " AND C_VALUE1='" + code + "'";
            if (NoQuery(sql) == 0)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public QS_PLAN_DETAILS Find(decimal id)
        {
            var sql = "SELECT * FROM QS_PLAN_DETAILS WHERE N_ID=" + id;
            var dt = Query(sql);
            if (dt.Rows.Count != 1)
            {
                return null;
            }
            var entity = new QS_PLAN_DETAILS();
            entity.N_ID = Convert.ToDecimal(dt.Rows[0]["N_ID"]);
            entity.N_PRO_ID = Convert.ToDecimal(dt.Rows[0]["N_PRO_ID"]);
            entity.C_VALUE1 = dt.Rows[0]["C_VALUE1"] == DBNull.Value ? "" : Convert.ToString(dt.Rows[0]["C_VALUE1"]);
            entity.C_VALUE2 = dt.Rows[0]["C_VALUE2"] == DBNull.Value ? "" : Convert.ToString(dt.Rows[0]["C_VALUE2"]);
            entity.C_VALUE3 = dt.Rows[0]["C_VALUE3"] == DBNull.Value ? "" : Convert.ToString(dt.Rows[0]["C_VALUE3"]);
            entity.C_VALUE4 = dt.Rows[0]["C_VALUE4"] == DBNull.Value ? "" : Convert.ToString(dt.Rows[0]["C_VALUE4"]);
            entity.C_VALUE5 = dt.Rows[0]["C_VALUE5"] == DBNull.Value ? "" : Convert.ToString(dt.Rows[0]["C_VALUE5"]);
            entity.C_VALUE6 = dt.Rows[0]["C_VALUE6"] == DBNull.Value ? "" : Convert.ToString(dt.Rows[0]["C_VALUE6"]);
            entity.C_VALUE7 = dt.Rows[0]["C_VALUE7"] == DBNull.Value ? "" : Convert.ToString(dt.Rows[0]["C_VALUE7"]);
            entity.C_VALUE8 = dt.Rows[0]["C_VALUE8"] == DBNull.Value ? "" : Convert.ToString(dt.Rows[0]["C_VALUE8"]);
            entity.C_VALUE9 = dt.Rows[0]["C_VALUE9"] == DBNull.Value ? "" : Convert.ToString(dt.Rows[0]["C_VALUE9"]);
            entity.C_VALUE10 = dt.Rows[0]["C_VALUE10"] == DBNull.Value ? "" : Convert.ToString(dt.Rows[0]["C_VALUE10"]);
            entity.C_VALUE11 = dt.Rows[0]["C_VALUE11"] == DBNull.Value ? "" : Convert.ToString(dt.Rows[0]["C_VALUE11"]);
            entity.N_STATE = Convert.ToDecimal(dt.Rows[0]["N_STATE"]);
            entity.N_PLAN_ID = Convert.ToDecimal(dt.Rows[0]["N_PLAN_ID"]);

            return entity;
        }

        #endregion 基础

        //合计
        public decimal FindSelectbyId(string c_valueT, string valueT, string state, int applyId)
        {
            string sql = "";
            if (valueT == "T1")
            {
                sql = string.Format(@"SELECT T1.C_VALUE1, SUM(" + c_valueT + @") AS 总和
  FROM QS_PLAN_DETAILS T1, QS_PROJECT T2
 WHERE T1.N_PLAN_ID = {0},
   and T1.C_VALUE1 = '{1}'
   and T1.N_STATE = {2},
   and T1.C_VALUE3 = T2.C_PRONAME
 GROUP BY T1.C_VALUE1", applyId, valueT, state);
            }
            else
            {
                sql = string.Format(@"SELECT T1.C_VALUE1, SUM(" + c_valueT + @") AS 总和
  FROM QS_PLAN_DETAILS T1
 WHERE T1.N_PLAN_ID = {0},
   and T1.C_VALUE1 = '{1}'
   and T1.N_STATE = {2}
 GROUP BY T1.C_VALUE1", applyId, valueT, state);
            }
            var dt = Query(sql);
            decimal stringName = 0;
            if (dt.Rows.Count > 0)
            {
                stringName = Convert.ToDecimal(dt.Rows[0][1]);
            }
            return stringName;
        }

        #region 周资金相关

        #region (T1)营业收入

        public List<QS_PLAN_DETAILS1> FindRangeWeek1(int applyId, int proId)
        {
            var sql = @"SELECT
            T1.C_VALUE2 AS T_Index,
            T1.C_VALUE3 AS T_Name,
            T1.C_VALUE4 AS T_SaleNumber,
            T1.C_VALUE5 AS T_UnitePrice,
            T1.C_VALUE6 AS T_SumPrice,
            T1.C_VALUE7 AS T_Flow,
            T1.C_VALUE8 AS T_Remark/*,
            T1.C_VALUE9 AS T_ParentIndex*/
            FROM QS_PLAN_DETAILS T1 WHERE T1.N_PLAN_ID=" + applyId;
            sql += " AND T1.N_STATE=3 AND T1.C_VALUE1='T1' AND T1.n_Pro_Id=" + proId;
            //sql += " AND T1.N_STATE=3 AND T1.C_VALUE1='T1'";
            sql += " ORDER BY T1.C_VALUE2";
            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAILS1>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAILS1();
                entity.Index = Convert.ToInt32(row["T_INDEX"]);
                if (row["T_Name"] == DBNull.Value)
                {
                    entity.Name = "";
                }
                else
                {
                    entity.Name = Convert.ToString(row["T_Name"]);
                }
                if (row["T_UnitePrice"] == DBNull.Value)
                {
                    entity.UnitePrice = null;
                }
                else
                {
                    entity.UnitePrice = Convert.ToDecimal(row["T_UnitePrice"]);
                }
                if (row["T_SaleNumber"] == DBNull.Value)
                {
                    entity.SaleNumber = null;
                }
                else
                {
                    entity.SaleNumber = Convert.ToInt32(row["T_SaleNumber"]);
                }

                if (row["T_Flow"] == DBNull.Value)
                {
                    entity.Flow = "";
                }
                else
                {
                    entity.Flow = Convert.ToString(row["T_Flow"]);
                }
                if (row["T_Remark"] == DBNull.Value)
                {
                    entity.Remark = "";
                }
                else
                {
                    entity.Remark = Convert.ToString(row["T_Remark"]);
                }
                //if (row["T_ParentIndex"] == DBNull.Value)
                //{
                //    entity.ParentIndex = null;
                //}
                //else
                //{
                //    entity.ParentIndex = Convert.ToInt32(row["T_ParentIndex"]);
                //}
                entity.SumPrice = Convert.ToDecimal(row["T_SumPrice"]);
                entitys.Add(entity);
            }
            return entitys;
        }

        public void UpdateOrCreateRangeWeek1(int applyId, int proId, List<QS_PLAN_DETAILS1> data)
        {
            //先清空再添加
            DeleteByApplyId(applyId, proId, 3, "T1");
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAILS();
                entity.C_VALUE1 = "T1";
                entity.C_VALUE2 = item.Index.ToString();
                entity.C_VALUE3 = item.Name;
                entity.C_VALUE4 = item.SaleNumber.ToString();
                entity.C_VALUE5 = item.UnitePrice.ToString();
                entity.C_VALUE6 = item.SumPrice.ToString();
                entity.C_VALUE7 = item.Flow.ToString();
                entity.C_VALUE8 = item.Remark.ToString();
                entity.N_PLAN_ID = applyId;
                entity.N_PRO_ID = proId;
                entity.N_STATE = 3;
                //if (item.ParentIndex == null)
                //{
                //    entity.C_VALUE9 = null;
                //}
                //else
                //{
                //    entity.C_VALUE9 = item.ParentIndex.ToString();
                //}
                if (Insert(entity))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"SELECT
	T2.N_WEEKDETAIL_ID AS ID,
	T3.C_PRONAME AS NAME,T2.N_PROID AS PROID
FROM
	QS_PLANWEEKAPPLY T1
INNER JOIN QS_PLANWEEK_DETAIL T2 ON T1.N_WEEKAPP_ID = T2.N_WEEKAPP_ID
INNER JOIN QS_PROJECT T3 ON T2.N_PROID = T3.N_PROID
INNER JOIN QS_PLAN_DETAILS T4 ON T1.N_WEEKAPP_ID = T4.N_PLAN_ID AND T3.C_DETAILS_CODE=T4.C_VALUE1
WHERE
	T1.N_WEEKAPP_ID={0} AND
	T4.C_VALUE1 = 'T1' AND
    T4.N_STATE = 3
GROUP BY T2.N_WEEKDETAIL_ID,T3.C_PRONAME,T2.N_PROID
ORDER BY T2.N_WEEKDETAIL_ID";
            sql = string.Format(sql, applyId);
            var dt = Query(sql);
            foreach (DataRow row in dt.Rows)
            {
                var id = Convert.ToString(row["ID"]);
                var name = Convert.ToString(row["NAME"]);
                var pro = Convert.ToInt32(row["PROID"]);
                decimal money = 0;
                foreach (var item in data.Where(a => proId == pro))
                {
                    if (item.SumPrice == null)
                    {
                        continue;
                    }
                    money += Convert.ToDecimal(item.SumPrice);
                }
                if (proId == pro)
                {
                    sql = "UPDATE QS_PLANWEEK_DETAIL SET N_AMOUNT=" + money + " WHERE N_WEEKDETAIL_ID=" + id;
                    NoQuery(sql);
                }
            }
            //foreach (DataRow row in dt.Rows)
            //{
            //    var id = Convert.ToString(row["ID"]);
            //    var name = Convert.ToString(row["NAME"]);
            //    decimal money = 0;
            //    var ientity = data.SingleOrDefault(a => a.Name.Trim() == name.Trim());// a.ParentIndex == null &&
            //    if (ientity != null)
            //    {
            //        money = ientity.SumPrice;
            //    }
            //    sql = "UPDATE QS_PLANWEEK_DETAIL SET N_AMOUNT=" + money + " WHERE N_WEEKDETAIL_ID=" + id;
            //    NoQuery(sql);
            //}
        }

        #endregion (T1)营业收入

        #region (T2)预付气款

        public List<QS_PLAN_DETAILS2> FindRangeWeek2(int applyId, int proId)
        {
            var sql = @"SELECT
            T1.C_VALUE2 AS C_VALUE1,
            T1.C_VALUE3 AS C_VALUE2,
            T1.C_VALUE4 AS C_VALUE3,
            T1.C_VALUE5 AS C_VALUE4,
            T1.C_VALUE6 AS C_VALUE5,
            T1.C_VALUE7 AS C_VALUE6,
            T1.C_VALUE8 AS C_VALUE7
            FROM QS_PLAN_DETAILS T1 WHERE T1.N_PLAN_ID=" + applyId;
            sql += " AND T1.N_STATE=3 AND T1.C_VALUE1='T2' AND T1.n_Pro_Id=" + proId;
            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAILS2>();

            #region entity

            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAILS2();
                if (row["C_VALUE1"] == DBNull.Value)
                {
                    entity.VALUE1 = null;
                }
                else
                {
                    entity.VALUE1 = row["C_VALUE1"].ToString();
                }
                if (row["C_VALUE2"] == DBNull.Value)
                {
                    entity.VALUE2 = null;
                }
                else
                {
                    entity.VALUE2 = row["C_VALUE2"].ToString();
                }
                if (row["C_VALUE3"] == DBNull.Value)
                {
                    entity.VALUE3 = null;
                }
                else
                {
                    entity.VALUE3 = Convert.ToInt32(row["C_VALUE3"].ToString());
                }
                if (row["C_VALUE4"] == DBNull.Value)
                {
                    entity.VALUE4 = null;
                }
                else
                {
                    entity.VALUE4 = Convert.ToDecimal(row["C_VALUE4"].ToString());
                }
                if (row["C_VALUE5"] == DBNull.Value)
                {
                    entity.VALUE5 = 0;
                }
                else
                {
                    entity.VALUE5 = Convert.ToDecimal(row["C_VALUE5"].ToString());
                }

                if (row["C_VALUE6"] == DBNull.Value)
                {
                    entity.VALUE6 = null;
                }
                else
                {
                    entity.VALUE6 = row["C_VALUE6"].ToString();
                }
                if (row["C_VALUE7"] == DBNull.Value)
                {
                    entity.VALUE7 = null;
                }
                else
                {
                    entity.VALUE7 = row["C_VALUE7"].ToString();
                }
                entitys.Add(entity);
            }

            #endregion entity

            //foreach (System.Data.DataRow row in dt.Rows)
            //{
            //    var entity = new QS_PLAN_DETAILS2();
            //    entity.VALUE1 = "T2";
            //    entity.VALUE2 = row["VALUE2"].ToString();
            //    entity.VALUE3 = Convert.ToInt32(row["VALUE3"].ToString());
            //    entity.VALUE4 = Convert.ToDecimal(row["VALUE4"].ToString());
            //    entity.VALUE5 = Convert.ToDecimal(row["VALUE5"].ToString());
            //    entity.VALUE6 = row["VALUE6"].ToString();
            //    entity.VALUE7 = row["VALUE7"].ToString();
            //    entitys.Add(entity);
            //}
            return entitys;
        }

        public void UpdateOrCreateRangeWeek2(int applyId, int proId, List<QS_PLAN_DETAILS2> data)
        {
            //先清空再添加
            DeleteByApplyId(applyId, 11, 3, "T2");
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAILS();
                entity.C_VALUE1 = "T2";
                entity.C_VALUE2 = item.VALUE1;
                entity.C_VALUE3 = item.VALUE2;
                entity.C_VALUE4 = item.VALUE3.ToString();
                entity.C_VALUE5 = item.VALUE4.ToString();
                entity.C_VALUE6 = item.VALUE5.ToString();
                entity.C_VALUE7 = item.VALUE6;
                entity.C_VALUE8 = item.VALUE7;
                entity.N_PLAN_ID = applyId;
                entity.N_PRO_ID = proId;
                entity.N_STATE = 3;

                if (Insert(entity))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"SELECT T2.N_WEEKDETAIL_ID AS ID, T3.C_PRONAME AS NAME, T1.N_WEEKAPP_ID,T2.N_PROID AS PROID
  FROM QS_PLANWEEKAPPLY T1
 INNER JOIN QS_PLANWEEK_DETAIL T2
    ON T1.N_WEEKAPP_ID = T2.N_WEEKAPP_ID
   AND T1.N_WEEKAPP_ID = {0}
 INNER JOIN QS_PROJECT T3
    ON T2.N_PROID = T3.N_PROID
 INNER JOIN QS_PLAN_DETAILS T4
    ON T1.N_WEEKAPP_ID = T4.N_PLAN_ID
   AND T3.C_DETAILS_CODE = T4.C_VALUE1
   AND T4.N_STATE = 3
   AND T4.C_VALUE1 = 'T2'";
            sql = string.Format(sql, applyId);
            var dt = Query(sql);
            foreach (DataRow row in dt.Rows)
            {
                var id = Convert.ToString(row["ID"]);
                var pro = Convert.ToString(row["PROID"]);
                var name = Convert.ToString(row["NAME"]);
                decimal money = 0;
                foreach (var item in data)
                {
                    if (item.VALUE5.ToString() == "")
                    {
                        continue;
                    }
                    money += Convert.ToDecimal(item.VALUE5);
                }
                sql = "UPDATE QS_PLANWEEK_DETAIL SET N_AMOUNT=" + money + " WHERE N_WEEKDETAIL_ID=" + id;
                NoQuery(sql);
            }
            //var id = Convert.ToString(dt.Rows[0]["ID"]);
            //sql = "UPDATE QS_PLANWEEK_DETAIL SET N_AMOUNT=" + money + " WHERE N_MDETAIL_ID=" + id;
            //NoQuery(sql);
        }

        #endregion (T2)预付气款

        #region (T3)安装外包费用明细表二

        public List<QS_PLAN_DETAILS3> FindRangeWeek3(int applyId, int proId)
        {
            var sql = @"SELECT
            T1.C_VALUE2 AS C_VALUE1,
            T1.C_VALUE3 AS C_VALUE2,
            T1.C_VALUE4 AS C_VALUE3,
            T1.C_VALUE5 AS C_VALUE4,
            T1.C_VALUE6 AS C_VALUE5,
            T1.C_VALUE7 AS C_VALUE6,
            T1.C_VALUE8 AS C_VALUE7,
            T1.C_VALUE9 AS C_VALUE9,
            T1.C_VALUE10 AS C_VALUE12,
            T1.C_VALUE11 AS C_VALUE13
            FROM QS_PLAN_DETAILS T1 WHERE T1.N_PLAN_ID=" + applyId;
            sql += " AND T1.N_STATE=3 AND T1.C_VALUE1='T3' AND T1.n_Pro_Id=" + proId + " ORDER BY T1.C_VALUE2";
            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAILS3>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAILS3();
                entity.VALUE1 = row["C_VALUE1"].ToString();
                entity.VALUE2 = row["C_VALUE2"].ToString();
                entity.VALUE3 = row["C_VALUE3"].ToString();
                entity.VALUE4 = row["C_VALUE4"].ToString();
                entity.VALUE5 = row["C_VALUE5"].ToString();
                entity.VALUE6 = row["C_VALUE6"].ToString();
                entity.VALUE7 = row["C_VALUE7"].ToString();
                entity.VALUE9 = row["C_VALUE9"].ToString();
                entity.VALUE12 = row["C_VALUE12"].ToString();
                entity.VALUE13 = row["C_VALUE13"].ToString();

                entitys.Add(entity);
            }
            return entitys;
        }

        public void UpdateOrCreateRangeWeek3(int applyId, int proId, List<QS_PLAN_DETAILS3> data)
        {
            //先清空再添加
            DeleteByApplyId(applyId, proId, 3, "T3");
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAILS();
                entity.C_VALUE1 = "T3";
                entity.C_VALUE2 = item.VALUE1;
                entity.C_VALUE3 = item.VALUE2;
                entity.C_VALUE4 = item.VALUE3;
                entity.C_VALUE5 = item.VALUE4;
                entity.C_VALUE6 = item.VALUE5;
                entity.C_VALUE7 = item.VALUE6;
                entity.C_VALUE8 = item.VALUE7;
                entity.C_VALUE9 = item.VALUE9;
                entity.C_VALUE10 = item.VALUE12;
                entity.C_VALUE11 = item.VALUE13;
                entity.N_PLAN_ID = applyId;
                entity.N_PRO_ID = proId;
                entity.N_STATE = 3;
                if (Insert(entity))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"SELECT
	T2.N_WEEKDETAIL_ID AS ID,
	T3.C_PRONAME AS NAME,T2.N_PROID AS PROID
FROM
	QS_PLANWEEKAPPLY T1
INNER JOIN QS_PLANWEEK_DETAIL T2 ON T1.N_WEEKAPP_ID = T2.N_WEEKAPP_ID
INNER JOIN QS_PROJECT T3 ON T2.N_PROID = T3.N_PROID
INNER JOIN QS_PLAN_DETAILS T4 ON T1.N_WEEKAPP_ID = T4.N_PLAN_ID AND T3.C_DETAILS_CODE=T4.C_VALUE1
WHERE
	T1.N_WEEKAPP_ID={0} AND
	T4.C_VALUE1 = 'T3' AND
    T4.N_STATE = 3
GROUP BY T2.N_WEEKDETAIL_ID,T3.C_PRONAME,T2.N_PROID
ORDER BY T2.N_WEEKDETAIL_ID";
            sql = string.Format(sql, applyId);
            var dt = Query(sql);
            foreach (DataRow row in dt.Rows)
            {
                var id = Convert.ToString(row["ID"]);
                var name = Convert.ToString(row["NAME"]);
                var pro = Convert.ToInt32(row["PROID"]);
                decimal money = 0;
                foreach (var item in data.Where(a => proId == pro))//a.VALUE3.Trim() == name.Trim()))
                {
                    if (item.VALUE9 == "")
                    {
                        continue;
                    }
                    money += Convert.ToDecimal(item.VALUE9);
                }
                if (proId == pro)
                {
                    sql = "UPDATE QS_PLANWEEK_DETAIL SET N_AMOUNT=" + money + " WHERE N_WEEKDETAIL_ID=" + id;
                    NoQuery(sql);
                }
            }
            //foreach (DataRow row in dt.Rows)
            //{
            //    var id = Convert.ToString(row["ID"]);
            //    var name = Convert.ToString(row["NAME"]);
            //    decimal money = 0;
            //    foreach (var item in data.Where(a => a.VALUE3.Trim() == name.Trim()))
            //    {
            //        if (item.VALUE9 == "")
            //        {
            //            continue;
            //        }
            //        money += Convert.ToDecimal(item.VALUE9);
            //    }
            //    sql = "UPDATE QS_PLANWEEK_DETAIL SET N_AMOUNT=" + money + " WHERE N_WEEKDETAIL_ID=" + id;
            //    NoQuery(sql);
            //}
        }

        #endregion (T3)安装外包费用明细表二

        #region (T4)投资项目支出

        public List<QS_PLAN_DETAILS3> FindRangeWeek4(int applyId, int proId)
        {
            var sql = @"SELECT
            T1.C_VALUE2 AS C_VALUE1,
            T1.C_VALUE3 AS C_VALUE2,
            T1.C_VALUE4 AS C_VALUE3,
            T1.C_VALUE5 AS C_VALUE4,
            T1.C_VALUE6 AS C_VALUE5,
            T1.C_VALUE7 AS C_VALUE6,
            T1.C_VALUE8 AS C_VALUE7,
            T1.C_VALUE9 AS C_VALUE9,
            T1.C_VALUE10 AS C_VALUE12,
            T1.C_VALUE11 AS C_VALUE13
            FROM QS_PLAN_DETAILS T1 WHERE T1.N_PLAN_ID=" + applyId;
            sql += " AND T1.N_STATE=3 AND T1.C_VALUE1='T4' AND T1.n_Pro_Id=" + proId + " ORDER BY T1.C_VALUE2";
            //sql += " AND T1.N_STATE=3 AND T1.C_VALUE1='T4'";
            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAILS3>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAILS3();
                entity.VALUE1 = row["C_VALUE1"].ToString();
                entity.VALUE2 = row["C_VALUE2"].ToString();
                entity.VALUE3 = row["C_VALUE3"].ToString();
                entity.VALUE4 = row["C_VALUE4"].ToString();
                entity.VALUE5 = row["C_VALUE5"].ToString();
                entity.VALUE6 = row["C_VALUE6"].ToString();
                entity.VALUE7 = row["C_VALUE7"].ToString();
                entity.VALUE9 = row["C_VALUE9"].ToString();
                entity.VALUE12 = row["C_VALUE12"].ToString();
                entity.VALUE13 = row["C_VALUE13"].ToString();

                entitys.Add(entity);
            }
            return entitys;
        }

        public void UpdateOrCreateRangeWeek4(int applyId, int proId, List<QS_PLAN_DETAILS3> data)
        {
            //先清空再添加
            DeleteByApplyId(applyId, 14, 2, "T4");
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAILS();
                entity.C_VALUE1 = "T4";
                entity.C_VALUE2 = item.VALUE1;
                entity.C_VALUE3 = item.VALUE2;
                entity.C_VALUE4 = item.VALUE3;
                entity.C_VALUE5 = item.VALUE4;
                entity.C_VALUE6 = item.VALUE5;
                entity.C_VALUE7 = item.VALUE6;
                entity.C_VALUE8 = item.VALUE7;
                entity.C_VALUE9 = item.VALUE9;
                entity.C_VALUE10 = item.VALUE12;
                entity.C_VALUE11 = item.VALUE13;
                entity.N_PLAN_ID = applyId;
                entity.N_PRO_ID = proId;
                entity.N_STATE = 3;
                if (Insert(entity))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"SELECT
	T2.N_WEEKDETAIL_ID AS ID,
	T3.C_PRONAME AS NAME,T2.N_PROID AS PROID
FROM
	QS_PLANWEEKAPPLY T1
INNER JOIN QS_PLANWEEK_DETAIL T2 ON T1.N_WEEKAPP_ID = T2.N_WEEKAPP_ID
INNER JOIN QS_PROJECT T3 ON T2.N_PROID = T3.N_PROID
INNER JOIN QS_PLAN_DETAILS T4 ON T1.N_WEEKAPP_ID = T4.N_PLAN_ID AND T3.C_DETAILS_CODE=T4.C_VALUE1
WHERE
	T1.N_WEEKAPP_ID={0} AND
	T4.C_VALUE1 = 'T4' AND
    T4.N_STATE = 3
GROUP BY T2.N_WEEKDETAIL_ID,T3.C_PRONAME,T2.N_PROID
ORDER BY T2.N_WEEKDETAIL_ID";
            sql = string.Format(sql, applyId);
            var dt = Query(sql);
            foreach (DataRow row in dt.Rows)
            {
                var id = Convert.ToString(row["ID"]);
                var pro = Convert.ToInt32(row["PROID"]);
                var name = Convert.ToString(row["NAME"]);
                decimal money = 0;
                foreach (var item in data)
                {
                    if (item.VALUE9 == "")
                    {
                        continue;
                    }
                    money += Convert.ToDecimal(item.VALUE9);
                }
                sql = "UPDATE QS_PLANWEEK_DETAIL SET N_AMOUNT=" + money + " WHERE N_WEEKDETAIL_ID=" + id;
                NoQuery(sql);
            }
        }

        #endregion (T4)投资项目支出

        #region (T5)其他产品及服务

        public List<QS_PLAN_DETAILS3> FindRangeWeek5(int applyId, int proId)
        {
            var sql = @"SELECT
            T1.C_VALUE2 AS C_VALUE1,
            T1.C_VALUE3 AS C_VALUE2,
            T1.C_VALUE4 AS C_VALUE3,
            T1.C_VALUE5 AS C_VALUE4,
            T1.C_VALUE6 AS C_VALUE5,
            T1.C_VALUE7 AS C_VALUE6,
            T1.C_VALUE8 AS C_VALUE7,
            T1.C_VALUE9 AS C_VALUE9,
            T1.C_VALUE10 AS C_VALUE12,
            T1.C_VALUE11 AS C_VALUE13
            FROM QS_PLAN_DETAILS T1 WHERE T1.N_PLAN_ID=" + applyId;
            sql += " AND T1.N_STATE=3 AND T1.C_VALUE1='T5' AND T1.n_Pro_Id=" + proId;
            //sql += " AND T1.N_STATE=3 AND T1.C_VALUE1='T5'";
            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAILS3>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAILS3();
                entity.VALUE1 = row["C_VALUE1"].ToString();
                entity.VALUE2 = row["C_VALUE2"].ToString();
                entity.VALUE3 = row["C_VALUE3"].ToString();
                entity.VALUE4 = row["C_VALUE4"].ToString();
                entity.VALUE5 = row["C_VALUE5"].ToString();
                entity.VALUE6 = row["C_VALUE6"].ToString();
                entity.VALUE7 = row["C_VALUE7"].ToString();
                entity.VALUE9 = row["C_VALUE9"].ToString();
                entity.VALUE12 = row["C_VALUE12"].ToString();
                entity.VALUE13 = row["C_VALUE13"].ToString();

                entitys.Add(entity);
            }
            return entitys;
        }

        public void UpdateOrCreateRangeWeek5(int applyId, int proId, List<QS_PLAN_DETAILS3> data)
        {
            //先清空再添加
            DeleteByApplyId(applyId, proId, 3, "T5");
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAILS();
                entity.C_VALUE1 = "T5";
                entity.C_VALUE2 = item.VALUE1;
                entity.C_VALUE3 = item.VALUE2;
                entity.C_VALUE4 = item.VALUE3;
                entity.C_VALUE5 = item.VALUE4;
                entity.C_VALUE6 = item.VALUE5;
                entity.C_VALUE7 = item.VALUE6;
                entity.C_VALUE8 = item.VALUE7;
                entity.C_VALUE9 = item.VALUE9;
                entity.C_VALUE10 = item.VALUE12;
                entity.C_VALUE11 = item.VALUE13;
                entity.N_PLAN_ID = applyId;
                entity.N_PRO_ID = proId;
                entity.N_STATE = 3;
                if (Insert(entity))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"SELECT
	T2.N_WEEKDETAIL_ID AS ID,
	T3.C_PRONAME AS NAME, T2.N_PROID AS PROID
FROM
	QS_PLANWEEKAPPLY T1
INNER JOIN QS_PLANWEEK_DETAIL T2 ON T1.N_WEEKAPP_ID = T2.N_WEEKAPP_ID
INNER JOIN QS_PROJECT T3 ON T2.N_PROID = T3.N_PROID
INNER JOIN QS_PLAN_DETAILS T4 ON T1.N_WEEKAPP_ID = T4.N_PLAN_ID AND T3.C_DETAILS_CODE=T4.C_VALUE1
WHERE
	T1.N_WEEKAPP_ID={0} AND
	T4.C_VALUE1 = 'T5' AND
    T4.N_STATE = 3
GROUP BY T2.N_WEEKDETAIL_ID,T3.C_PRONAME, T2.N_PROID
ORDER BY T2.N_WEEKDETAIL_ID";
            sql = string.Format(sql, applyId);
            var dt = Query(sql);
            foreach (DataRow row in dt.Rows)
            {
                var id = Convert.ToString(row["ID"]);
                var pro = Convert.ToString(row["PROID"]);
                var name = Convert.ToString(row["NAME"]);
                decimal money = 0;
                foreach (var item in data)
                {
                    if (item.VALUE9 == "")
                    {
                        continue;
                    }
                    money += Convert.ToDecimal(item.VALUE9);
                }
                sql = "UPDATE QS_PLANWEEK_DETAIL SET N_AMOUNT=" + money + " WHERE N_WEEKDETAIL_ID=" + id;
                NoQuery(sql);
            }
        }

        #endregion (T5)其他产品及服务

        #region (T6)投资项目支出

        public List<QS_PLAN_DETAILS3> FindRangeWeek6(int applyId, int proId)
        {
            var sql = @"SELECT
            T1.C_VALUE2 AS C_VALUE1,
            T1.C_VALUE3 AS C_VALUE2,
            T1.C_VALUE4 AS C_VALUE3,
            T1.C_VALUE5 AS C_VALUE4,
            T1.C_VALUE6 AS C_VALUE5,
            T1.C_VALUE7 AS C_VALUE6,
            T1.C_VALUE8 AS C_VALUE7,
            T1.C_VALUE9 AS C_VALUE9,
            T1.C_VALUE10 AS C_VALUE12,
            T1.C_VALUE11 AS C_VALUE13
            FROM QS_PLAN_DETAILS T1 WHERE T1.N_PLAN_ID=" + applyId;
            sql += " AND T1.N_STATE=3 AND T1.C_VALUE1='T6' AND T1.n_Pro_Id=" + proId;
            //sql += " AND T1.N_STATE=3 AND T1.C_VALUE1='T6'";
            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAILS3>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAILS3();
                entity.VALUE1 = row["C_VALUE1"].ToString();
                entity.VALUE2 = row["C_VALUE2"].ToString();
                entity.VALUE3 = row["C_VALUE3"].ToString();
                entity.VALUE4 = row["C_VALUE4"].ToString();
                entity.VALUE5 = row["C_VALUE5"].ToString();
                entity.VALUE6 = row["C_VALUE6"].ToString();
                entity.VALUE7 = row["C_VALUE7"].ToString();
                entity.VALUE9 = row["C_VALUE9"].ToString();
                entity.VALUE12 = row["C_VALUE12"].ToString();
                entity.VALUE13 = row["C_VALUE13"].ToString();

                entitys.Add(entity);
            }
            return entitys;
        }

        public void UpdateOrCreateRangeWeek6(int applyId, int proId, List<QS_PLAN_DETAILS3> data)
        {
            //先清空再添加
            DeleteByApplyId(applyId, proId, 3, "T6");
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAILS();
                entity.C_VALUE1 = "T6";
                entity.C_VALUE2 = item.VALUE1;
                entity.C_VALUE3 = item.VALUE2;
                entity.C_VALUE4 = item.VALUE3;
                entity.C_VALUE5 = item.VALUE4;
                entity.C_VALUE6 = item.VALUE5;
                entity.C_VALUE7 = item.VALUE6;
                entity.C_VALUE8 = item.VALUE7;
                entity.C_VALUE9 = item.VALUE9;
                entity.C_VALUE10 = item.VALUE12;
                entity.C_VALUE11 = item.VALUE13;
                entity.N_PLAN_ID = applyId;
                entity.N_PRO_ID = proId;
                entity.N_STATE = 3;
                if (Insert(entity))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"SELECT
	T2.N_WEEKDETAIL_ID AS ID,
	T3.C_PRONAME AS NAME,T2.N_PROID AS PROID
FROM
	QS_PLANWEEKAPPLY T1
INNER JOIN QS_PLANWEEK_DETAIL T2 ON T1.N_WEEKAPP_ID = T2.N_WEEKAPP_ID
INNER JOIN QS_PROJECT T3 ON T2.N_PROID = T3.N_PROID
INNER JOIN QS_PLAN_DETAILS T4 ON T1.N_WEEKAPP_ID = T4.N_PLAN_ID AND T3.C_DETAILS_CODE=T4.C_VALUE1
WHERE
	T1.N_WEEKAPP_ID={0} AND
	T4.C_VALUE1 = 'T6' AND
    T4.N_STATE = 3
GROUP BY T2.N_WEEKDETAIL_ID,T3.C_PRONAME,T2.N_PROID
ORDER BY T2.N_WEEKDETAIL_ID";
            sql = string.Format(sql, applyId);
            var dt = Query(sql);
            foreach (DataRow row in dt.Rows)
            {
                var id = Convert.ToString(row["ID"]);
                var name = Convert.ToString(row["NAME"]);
                var pro = Convert.ToInt32(row["PROID"]);
                decimal money = 0;
                foreach (var item in data.Where(a => proId == pro))
                {
                    if (item.VALUE9 == "")
                    {
                        continue;
                    }
                    money += Convert.ToDecimal(item.VALUE9);
                }
                if (proId == pro)
                {
                    sql = "UPDATE QS_PLANWEEK_DETAIL SET N_AMOUNT=" + money + " WHERE N_WEEKDETAIL_ID=" + id;
                    NoQuery(sql);
                }
            }
            //foreach (DataRow row in dt.Rows)
            //{
            //    var id = Convert.ToString(row["ID"]);
            //    var name = Convert.ToString(row["NAME"]);
            //    decimal money = 0;
            //    foreach (var item in data.Where(a => a.VALUE3.Trim() == name.Trim()))
            //    {
            //        if (item.VALUE9 == "")
            //        {
            //            continue;
            //        }
            //        money += Convert.ToDecimal(item.VALUE9);
            //    }
            //    sql = "UPDATE QS_PLANWEEK_DETAIL SET N_AMOUNT=" + money + " WHERE N_WEEKDETAIL_ID=" + id;
            //    NoQuery(sql);
            //}
        }

        #endregion (T6)投资项目支出

        #endregion 周资金相关

        #region 月资金相关

        #region (T1)营业收入

        public List<QS_PLAN_DETAILS1> FindRangeMonth1(int applyId, int proId)
        {
            var sql = @"SELECT
            T1.C_VALUE2 AS T_Index,
            T1.C_VALUE3 AS T_Name,
            T1.C_VALUE4 AS T_SaleNumber,
            T1.C_VALUE5 AS T_UnitePrice,
            T1.C_VALUE6 AS T_SumPrice,
            T1.C_VALUE7 AS T_Flow,
            T1.C_VALUE8 AS T_Remark/*,
            T1.C_VALUE9 AS T_ParentIndex*/
            FROM QS_PLAN_DETAILS T1 WHERE T1.N_PLAN_ID=" + applyId;
            sql += " AND T1.N_STATE=2 AND T1.C_VALUE1='T1' AND T1.n_Pro_Id=" + proId;
            sql += " ORDER BY T1.C_VALUE2";
            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAILS1>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAILS1();
                entity.Index = Convert.ToInt32(row["T_INDEX"]);
                if (row["T_Name"] == DBNull.Value)
                {
                    entity.Name = "";
                }
                else
                {
                    entity.Name = Convert.ToString(row["T_Name"]);
                }
                if (row["T_UnitePrice"] == DBNull.Value)
                {
                    entity.UnitePrice = null;
                }
                else
                {
                    entity.UnitePrice = Convert.ToDecimal(row["T_UnitePrice"]);
                }
                if (row["T_SaleNumber"] == DBNull.Value)
                {
                    entity.SaleNumber = null;
                }
                else
                {
                    entity.SaleNumber = Convert.ToInt32(row["T_SaleNumber"]);
                }

                if (row["T_Flow"] == DBNull.Value)
                {
                    entity.Flow = "";
                }
                else
                {
                    entity.Flow = Convert.ToString(row["T_Flow"]);
                }
                if (row["T_Remark"] == DBNull.Value)
                {
                    entity.Remark = "";
                }
                else
                {
                    entity.Remark = Convert.ToString(row["T_Remark"]);
                }
                //if (row["T_ParentIndex"] == DBNull.Value)
                //{
                //    entity.ParentIndex = null;
                //}
                //else
                //{
                //    entity.ParentIndex = Convert.ToInt32(row["T_ParentIndex"]);
                //}
                entity.SumPrice = Convert.ToDecimal(row["T_SumPrice"]);
                entitys.Add(entity);
            }
            return entitys;
        }

        public void UpdateOrCreateRangeMonth1(int applyId, int proId, List<QS_PLAN_DETAILS1> data)
        {
            //先清空再添加
            DeleteByApplyId(applyId, proId, 2, "T1");
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAILS();
                entity.C_VALUE1 = "T1";
                entity.C_VALUE2 = item.Index.ToString();
                entity.C_VALUE3 = item.Name;
                entity.C_VALUE4 = item.SaleNumber.ToString();
                entity.C_VALUE5 = item.UnitePrice.ToString();
                entity.C_VALUE6 = item.SumPrice.ToString();
                entity.C_VALUE7 = item.Flow.ToString();
                entity.C_VALUE8 = item.Remark.ToString();
                entity.N_PRO_ID = proId;
                entity.N_PLAN_ID = applyId;
                entity.N_STATE = 2;
                //if (item.ParentIndex == null)
                //{
                //    entity.C_VALUE9 = null;
                //}
                //else
                //{
                //    entity.C_VALUE9 = item.ParentIndex.ToString();
                //}
                if (Insert(entity))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"SELECT
	T2.N_MDETAIL_ID AS ID,
	T3.C_PRONAME AS NAME,T2.N_PROID AS PROID
FROM
	QS_PLANMONTHAPPLY T1
INNER JOIN QS_PLANMONTH_DETAIL T2 ON T1.N_MONTHAPP_ID = T2.N_MONTHAPP_ID
INNER JOIN QS_PROJECT T3 ON T2.N_PROID = T3.N_PROID
INNER JOIN QS_PLAN_DETAILS T4 ON T1.N_MONTHAPP_ID = T4.N_PLAN_ID AND T3.C_DETAILS_CODE=T4.C_VALUE1
WHERE
	T1.N_MONTHAPP_ID={0} AND
	T4.C_VALUE1 = 'T1' AND
    T4.N_STATE=2
GROUP BY T2.N_MDETAIL_ID,T3.C_PRONAME,T2.N_PROID
ORDER BY T2.N_MDETAIL_ID";
            sql = string.Format(sql, applyId);
            var dt = Query(sql);
            foreach (DataRow row in dt.Rows)
            {
                var id = Convert.ToString(row["ID"]);
                var name = Convert.ToString(row["NAME"]);
                var pro = Convert.ToInt32(row["PROID"]);
                decimal money = 0;
                foreach (var item in data.Where(a => proId == pro))
                {
                    if (item.SumPrice == null)
                    {
                        continue;
                    }
                    money += Convert.ToDecimal(item.SumPrice);
                }
                if (proId == pro)
                {
                    sql = "UPDATE QS_PLANMONTH_DETAIL SET N_AMOUNT=" + money + " WHERE N_MDETAIL_ID=" + id;
                    NoQuery(sql);
                }
            }
            //foreach (DataRow row in dt.Rows)
            //{
            //    var id = Convert.ToString(row["ID"]);
            //    var name = Convert.ToString(row["NAME"]);
            //    var pro = Convert.ToInt32(row["PROID"]);
            //    decimal money = 0;
            //    var ientity = data.SingleOrDefault(a => proId == pro);//a.ParentIndex == null && //a.Name == name);
            //    if (ientity != null)
            //    {
            //        money += ientity.SumPrice;
            //    }
            //    if (proId == pro)
            //    {
            //        sql = "UPDATE QS_PLANMONTH_DETAIL SET N_AMOUNT=" + money + " WHERE N_MDETAIL_ID=" + id;
            //        NoQuery(sql);
            //    }
            //}
        }

        #endregion (T1)营业收入

        #region (T2)预付气款

        public List<QS_PLAN_DETAILS2> FindRangeMonth2(int applyId, int proId)
        {
            var sql = @"SELECT
            T1.C_VALUE2 AS C_VALUE1,
            T1.C_VALUE3 AS C_VALUE2,
            T1.C_VALUE4 AS C_VALUE3,
            T1.C_VALUE5 AS C_VALUE4,
            T1.C_VALUE6 AS C_VALUE5,
            T1.C_VALUE7 AS C_VALUE6,
            T1.C_VALUE8 AS C_VALUE7
            FROM QS_PLAN_DETAILS T1 WHERE T1.N_PLAN_ID=" + applyId;
            sql += " AND T1.N_STATE=2 AND T1.C_VALUE1='T2' AND T1.n_Pro_Id=" + proId;
            var dt = Query(sql);

            var entitys = new List<QS_PLAN_DETAILS2>();

            #region entity

            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAILS2();
                if (row["C_VALUE1"] == DBNull.Value)
                {
                    entity.VALUE1 = null;
                }
                else
                {
                    entity.VALUE1 = row["C_VALUE1"].ToString();
                }
                if (row["C_VALUE2"] == DBNull.Value)
                {
                    entity.VALUE2 = null;
                }
                else
                {
                    entity.VALUE2 = row["C_VALUE2"].ToString();
                }
                if (row["C_VALUE3"] == DBNull.Value)
                {
                    entity.VALUE3 = null;
                }
                else
                {
                    entity.VALUE3 = Convert.ToInt32(row["C_VALUE3"].ToString());
                }
                if (row["C_VALUE4"] == DBNull.Value)
                {
                    entity.VALUE4 = null;
                }
                else
                {
                    entity.VALUE4 = Convert.ToDecimal(row["C_VALUE4"].ToString());
                }
                if (row["C_VALUE5"] == DBNull.Value)
                {
                    entity.VALUE5 = 0;
                }
                else
                {
                    entity.VALUE5 = Convert.ToDecimal(row["C_VALUE5"].ToString());
                }

                if (row["C_VALUE6"] == DBNull.Value)
                {
                    entity.VALUE6 = null;
                }
                else
                {
                    entity.VALUE6 = row["C_VALUE6"].ToString();
                }
                if (row["C_VALUE7"] == DBNull.Value)
                {
                    entity.VALUE7 = null;
                }
                else
                {
                    entity.VALUE7 = row["C_VALUE7"].ToString();
                }
                entitys.Add(entity);
            }

            #endregion entity

            return entitys;
        }

        public void UpdateOrCreateRangeMonth2(int applyId, int proId, List<QS_PLAN_DETAILS2> data)
        {
            //先清空再添加
            DeleteByApplyId(applyId, proId, 2, "T2");
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAILS();
                entity.C_VALUE1 = "T2";
                entity.C_VALUE2 = item.VALUE1;
                entity.C_VALUE3 = item.VALUE2;
                entity.C_VALUE4 = item.VALUE3.ToString();
                entity.C_VALUE5 = item.VALUE4.ToString();
                entity.C_VALUE6 = item.VALUE5.ToString();
                entity.C_VALUE7 = item.VALUE6;
                entity.C_VALUE8 = item.VALUE7;
                entity.N_PRO_ID = proId;
                entity.N_PLAN_ID = applyId;
                entity.N_STATE = 2;
                if (Insert(entity))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"SELECT
	T2.N_MDETAIL_ID AS ID,
	T3.C_PRONAME AS NAME,T2.N_PROID AS PROID
FROM
	QS_PLANMONTHAPPLY T1
INNER JOIN QS_PLANMONTH_DETAIL T2 ON T1.N_MONTHAPP_ID = T2.N_MONTHAPP_ID
INNER JOIN QS_PROJECT T3 ON T2.N_PROID = T3.N_PROID
INNER JOIN QS_PLAN_DETAILS T4 ON T1.N_MONTHAPP_ID = T4.N_PLAN_ID AND T3.C_DETAILS_CODE=T4.C_VALUE1
WHERE
	T1.N_MONTHAPP_ID={0} AND
	T4.C_VALUE1 = 'T2' AND
    T4.N_STATE=2
GROUP BY T2.N_MDETAIL_ID,T3.C_PRONAME,T2.N_PROID
ORDER BY T2.N_MDETAIL_ID";
            sql = string.Format(sql, applyId);
            var dt = Query(sql);

            foreach (DataRow row in dt.Rows)
            {
                var id = Convert.ToString(row["ID"]);
                var name = Convert.ToString(row["NAME"]);
                var pro = Convert.ToString(row["PROID"]);
                decimal money = 0;
                foreach (var item in data)
                {
                    if (item.VALUE5.ToString() == "")
                    {
                        continue;
                    }
                    money += Convert.ToDecimal(item.VALUE5);
                }
                sql = "UPDATE QS_PLANMONTH_DETAIL SET N_AMOUNT=" + money + " WHERE N_MDETAIL_ID=" + id;
                NoQuery(sql);
            }
        }

        #endregion (T2)预付气款

        #region (T3)安装外包费用明细表

        public List<QS_PLAN_DETAILS3> FindRangeMonth3(int applyId, int proId)
        {
            var sql = @"SELECT
            T1.C_VALUE2 AS C_VALUE1,
            T1.C_VALUE3 AS C_VALUE2,
            T1.C_VALUE4 AS C_VALUE3,
            T1.C_VALUE5 AS C_VALUE4,
            T1.C_VALUE6 AS C_VALUE5,
            T1.C_VALUE7 AS C_VALUE6,
            T1.C_VALUE8 AS C_VALUE7,
            T1.C_VALUE9 AS C_VALUE9,
            T1.C_VALUE10 AS C_VALUE12,
            T1.C_VALUE11 AS C_VALUE13
            FROM QS_PLAN_DETAILS T1 WHERE T1.N_PLAN_ID=" + applyId;
            sql += " AND T1.N_STATE=2 AND T1.C_VALUE1='T3' AND T1.n_Pro_Id=" + proId + " ORDER BY T1.C_VALUE2";
            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAILS3>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAILS3();
                entity.VALUE1 = row["C_VALUE1"].ToString();
                entity.VALUE2 = row["C_VALUE2"].ToString();
                entity.VALUE3 = row["C_VALUE3"].ToString();
                entity.VALUE4 = row["C_VALUE4"].ToString();
                entity.VALUE5 = row["C_VALUE5"].ToString();
                entity.VALUE6 = row["C_VALUE6"].ToString();
                entity.VALUE7 = row["C_VALUE7"].ToString();
                entity.VALUE9 = row["C_VALUE9"].ToString();
                entity.VALUE12 = row["C_VALUE12"].ToString();
                entity.VALUE13 = row["C_VALUE13"].ToString();

                entitys.Add(entity);
            }
            return entitys;
        }

        public void UpdateOrCreateRangeMonth3(int applyId, int proId, List<QS_PLAN_DETAILS3> data)
        {
            //先清空再添加
            DeleteByApplyId(applyId, proId, 2, "T3");
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAILS();
                entity.C_VALUE1 = "T3";
                entity.C_VALUE2 = item.VALUE1;
                entity.C_VALUE3 = item.VALUE2;
                entity.C_VALUE4 = item.VALUE3;
                entity.C_VALUE5 = item.VALUE4;
                entity.C_VALUE6 = item.VALUE5;
                entity.C_VALUE7 = item.VALUE6;
                entity.C_VALUE8 = item.VALUE7;
                entity.C_VALUE9 = item.VALUE9;
                entity.C_VALUE10 = item.VALUE12;
                entity.C_VALUE11 = item.VALUE13;
                entity.N_PRO_ID = proId;
                entity.N_PLAN_ID = applyId;
                entity.N_STATE = 2;
                if (Insert(entity))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"SELECT
	T2.N_MDETAIL_ID AS ID,
	T3.C_PRONAME AS NAME,T2.N_PROID AS PROID
FROM
	QS_PLANMONTHAPPLY T1
INNER JOIN QS_PLANMONTH_DETAIL T2 ON T1.N_MONTHAPP_ID = T2.N_MONTHAPP_ID
INNER JOIN QS_PROJECT T3 ON T2.N_PROID = T3.N_PROID
INNER JOIN QS_PLAN_DETAILS T4 ON T1.N_MONTHAPP_ID = T4.N_PLAN_ID AND T3.C_DETAILS_CODE=T4.C_VALUE1
WHERE
	T1.N_MONTHAPP_ID={0} AND
	T4.C_VALUE1 = 'T3' AND
    T4.N_STATE=2
GROUP BY T2.N_MDETAIL_ID,T3.C_PRONAME,T2.N_PROID
ORDER BY T2.N_MDETAIL_ID";
            sql = string.Format(sql, applyId);
            var dt = Query(sql);

            foreach (DataRow row in dt.Rows)
            {
                var id = Convert.ToString(row["ID"]);
                var name = Convert.ToString(row["NAME"]);
                var pro = Convert.ToInt32(row["PROID"]);
                decimal money = 0;
                foreach (var item in data.Where(a => proId == pro))//a.VALUE3.Trim() == name.Trim()))
                {
                    if (item.VALUE9 == "")
                    {
                        continue;
                    }
                    money += Convert.ToDecimal(item.VALUE9);
                }
                if (proId == pro)
                {
                    sql = "UPDATE QS_PLANMONTH_DETAIL SET N_AMOUNT=" + money + " WHERE N_MDETAIL_ID=" + id;
                    NoQuery(sql);
                }
            }
        }

        #endregion (T3)安装外包费用明细表

        #region (T4)投资项目支出

        public List<QS_PLAN_DETAILS3> FindRangeMonth4(int applyId, int proId)
        {
            var sql = @"SELECT
            T1.C_VALUE2 AS C_VALUE1,
            T1.C_VALUE3 AS C_VALUE2,
            T1.C_VALUE4 AS C_VALUE3,
            T1.C_VALUE5 AS C_VALUE4,
            T1.C_VALUE6 AS C_VALUE5,
            T1.C_VALUE7 AS C_VALUE6,
            T1.C_VALUE8 AS C_VALUE7,
            T1.C_VALUE9 AS C_VALUE9,
            T1.C_VALUE10 AS C_VALUE12,
            T1.C_VALUE11 AS C_VALUE13
            FROM QS_PLAN_DETAILS T1 WHERE T1.N_PLAN_ID=" + applyId;
            sql += " AND T1.N_STATE=2 AND T1.C_VALUE1='T4' AND T1.n_Pro_Id=" + proId + " ORDER BY T1.C_VALUE2";
            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAILS3>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAILS3();
                entity.VALUE1 = row["C_VALUE1"].ToString();
                entity.VALUE2 = row["C_VALUE2"].ToString();
                entity.VALUE3 = row["C_VALUE3"].ToString();
                entity.VALUE4 = row["C_VALUE4"].ToString();
                entity.VALUE5 = row["C_VALUE5"].ToString();
                entity.VALUE6 = row["C_VALUE6"].ToString();
                entity.VALUE7 = row["C_VALUE7"].ToString();
                entity.VALUE9 = row["C_VALUE9"].ToString();
                entity.VALUE12 = row["C_VALUE12"].ToString();
                entity.VALUE13 = row["C_VALUE13"].ToString();

                entitys.Add(entity);
            }
            return entitys;
        }

        public void UpdateOrCreateRangeMonth4(int applyId, int proId, List<QS_PLAN_DETAILS3> data)
        {
            //先清空再添加
            DeleteByApplyId(applyId, proId, 2, "T4");
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAILS();
                entity.C_VALUE1 = "T4";
                entity.C_VALUE2 = item.VALUE1;
                entity.C_VALUE3 = item.VALUE2;
                entity.C_VALUE4 = item.VALUE3;
                entity.C_VALUE5 = item.VALUE4;
                entity.C_VALUE6 = item.VALUE5;
                entity.C_VALUE7 = item.VALUE6;
                entity.C_VALUE8 = item.VALUE7;
                entity.C_VALUE9 = item.VALUE9;
                entity.C_VALUE10 = item.VALUE12;
                entity.C_VALUE11 = item.VALUE13;
                entity.N_PRO_ID = proId;
                entity.N_PLAN_ID = applyId;
                entity.N_STATE = 2;
                if (Insert(entity))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"SELECT
	T2.N_MDETAIL_ID AS ID,
	T3.C_PRONAME AS NAME,T2.N_PROID AS PROID
FROM
	QS_PLANMONTHAPPLY T1
INNER JOIN QS_PLANMONTH_DETAIL T2 ON T1.N_MONTHAPP_ID = T2.N_MONTHAPP_ID
INNER JOIN QS_PROJECT T3 ON T2.N_PROID = T3.N_PROID
INNER JOIN QS_PLAN_DETAILS T4 ON T1.N_MONTHAPP_ID = T4.N_PLAN_ID AND T3.C_DETAILS_CODE=T4.C_VALUE1
WHERE
	T1.N_MONTHAPP_ID={0} AND
	T4.C_VALUE1 = 'T4' AND
    T4.N_STATE=2
GROUP BY T2.N_MDETAIL_ID,T3.C_PRONAME,T2.N_PROID
ORDER BY T2.N_MDETAIL_ID";
            sql = string.Format(sql, applyId);
            var dt = Query(sql);
            foreach (DataRow row in dt.Rows)
            {
                var id = Convert.ToString(row["ID"]);
                var pro = Convert.ToInt32(row["PROID"]);
                var name = Convert.ToString(row["NAME"]);
                decimal money = 0;
                foreach (var item in data)
                {
                    if (item.VALUE9 == "")
                    {
                        continue;
                    }
                    money += Convert.ToDecimal(item.VALUE9);
                }
                sql = "UPDATE QS_PLANMONTH_DETAIL SET N_AMOUNT=" + money + " WHERE N_MDETAIL_ID=" + id;
                NoQuery(sql);
            }
        }

        #endregion (T4)投资项目支出

        #region (T5)其他产品及服务

        public List<QS_PLAN_DETAILS3> FindRangeMonth5(int applyId, int proId)
        {
            var sql = @"SELECT
            T1.C_VALUE2 AS C_VALUE1,
            T1.C_VALUE3 AS C_VALUE2,
            T1.C_VALUE4 AS C_VALUE3,
            T1.C_VALUE5 AS C_VALUE4,
            T1.C_VALUE6 AS C_VALUE5,
            T1.C_VALUE7 AS C_VALUE6,
            T1.C_VALUE8 AS C_VALUE7,
            T1.C_VALUE9 AS C_VALUE9,
            T1.C_VALUE10 AS C_VALUE12,
            T1.C_VALUE11 AS C_VALUE13
            FROM QS_PLAN_DETAILS T1 WHERE T1.N_PLAN_ID=" + applyId;
            sql += " AND T1.N_STATE=2 AND T1.C_VALUE1='T5' AND T1.n_Pro_Id=" + proId;
            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAILS3>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAILS3();
                entity.VALUE1 = row["C_VALUE1"].ToString();
                entity.VALUE2 = row["C_VALUE2"].ToString();
                entity.VALUE3 = row["C_VALUE3"].ToString();
                entity.VALUE4 = row["C_VALUE4"].ToString();
                entity.VALUE5 = row["C_VALUE5"].ToString();
                entity.VALUE6 = row["C_VALUE6"].ToString();
                entity.VALUE7 = row["C_VALUE7"].ToString();
                entity.VALUE9 = row["C_VALUE9"].ToString();
                entity.VALUE12 = row["C_VALUE12"].ToString();
                entity.VALUE13 = row["C_VALUE13"].ToString();

                entitys.Add(entity);
            }
            return entitys;
        }

        public void UpdateOrCreateRangeMonth5(int applyId, int proId, List<QS_PLAN_DETAILS3> data)
        {
            //先清空再添加
            DeleteByApplyId(applyId, 171, 2, "T5");
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAILS();
                entity.C_VALUE1 = "T5";
                entity.C_VALUE2 = item.VALUE1;
                entity.C_VALUE3 = item.VALUE2;
                entity.C_VALUE4 = item.VALUE3;
                entity.C_VALUE5 = item.VALUE4;
                entity.C_VALUE6 = item.VALUE5;
                entity.C_VALUE7 = item.VALUE6;
                entity.C_VALUE8 = item.VALUE7;
                entity.C_VALUE9 = item.VALUE9;
                entity.C_VALUE10 = item.VALUE12;
                entity.C_VALUE11 = item.VALUE13;
                entity.N_PRO_ID = proId;
                entity.N_PLAN_ID = applyId;
                entity.N_STATE = 2;
                if (Insert(entity))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"SELECT
	T2.N_MDETAIL_ID AS ID,
	T3.C_PRONAME AS NAME, T2.N_PROID AS PROID
FROM
	QS_PLANMONTHAPPLY T1
INNER JOIN QS_PLANMONTH_DETAIL T2 ON T1.N_MONTHAPP_ID = T2.N_MONTHAPP_ID
INNER JOIN QS_PROJECT T3 ON T2.N_PROID = T3.N_PROID
INNER JOIN QS_PLAN_DETAILS T4 ON T1.N_MONTHAPP_ID = T4.N_PLAN_ID AND T3.C_DETAILS_CODE=T4.C_VALUE1
WHERE
	T1.N_MONTHAPP_ID={0} AND
	T4.C_VALUE1 = 'T5' AND
    T4.N_STATE=2
GROUP BY T2.N_MDETAIL_ID,T3.C_PRONAME, T2.N_PROID
ORDER BY T2.N_MDETAIL_ID";
            sql = string.Format(sql, applyId);
            var dt = Query(sql);
            foreach (DataRow row in dt.Rows)
            {
                var id = Convert.ToString(row["ID"]);
                var pro = Convert.ToInt32(row["PROID"]);
                var name = Convert.ToString(row["NAME"]);
                decimal money = 0;
                foreach (var item in data)
                {
                    if (item.VALUE9 == "")
                    {
                        continue;
                    }
                    money += Convert.ToDecimal(item.VALUE9);
                }
                sql = "UPDATE QS_PLANMONTH_DETAIL SET N_AMOUNT=" + money + " WHERE N_MDETAIL_ID=" + id;
                NoQuery(sql);
            }
        }

        #endregion (T5)其他产品及服务

        #region (T6)投资项目支出

        public List<QS_PLAN_DETAILS3> FindRangeMonth6(int applyId, int proId)
        {
            var sql = @"SELECT
            T1.C_VALUE2 AS C_VALUE1,
            T1.C_VALUE3 AS C_VALUE2,
            T1.C_VALUE4 AS C_VALUE3,
            T1.C_VALUE5 AS C_VALUE4,
            T1.C_VALUE6 AS C_VALUE5,
            T1.C_VALUE7 AS C_VALUE6,
            T1.C_VALUE8 AS C_VALUE7,
            T1.C_VALUE9 AS C_VALUE9,
            T1.C_VALUE10 AS C_VALUE12,
            T1.C_VALUE11 AS C_VALUE13
            FROM QS_PLAN_DETAILS T1 WHERE T1.N_PLAN_ID=" + applyId;
            sql += " AND T1.N_STATE=2 AND T1.C_VALUE1='T6' AND T1.n_Pro_Id=" + proId;
            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAILS3>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAILS3();
                entity.VALUE1 = row["C_VALUE1"].ToString();
                entity.VALUE2 = row["C_VALUE2"].ToString();
                entity.VALUE3 = row["C_VALUE3"].ToString();
                entity.VALUE4 = row["C_VALUE4"].ToString();
                entity.VALUE5 = row["C_VALUE5"].ToString();
                entity.VALUE6 = row["C_VALUE6"].ToString();
                entity.VALUE7 = row["C_VALUE7"].ToString();
                entity.VALUE9 = row["C_VALUE9"].ToString();
                entity.VALUE12 = row["C_VALUE12"].ToString();
                entity.VALUE13 = row["C_VALUE13"].ToString();

                entitys.Add(entity);
            }
            return entitys;
        }

        public void UpdateOrCreateRangeMonth6(int applyId, int proId, List<QS_PLAN_DETAILS3> data)
        {
            //先清空再添加
            DeleteByApplyId(applyId, proId, 2, "T6");
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAILS();
                entity.C_VALUE1 = "T6";
                entity.C_VALUE2 = item.VALUE1;
                entity.C_VALUE3 = item.VALUE2;
                entity.C_VALUE4 = item.VALUE3;
                entity.C_VALUE5 = item.VALUE4;
                entity.C_VALUE6 = item.VALUE5;
                entity.C_VALUE7 = item.VALUE6;
                entity.C_VALUE8 = item.VALUE7;
                entity.C_VALUE9 = item.VALUE9;
                entity.C_VALUE10 = item.VALUE12;
                entity.C_VALUE11 = item.VALUE13;
                entity.N_PRO_ID = proId;
                entity.N_PLAN_ID = applyId;
                entity.N_STATE = 2;
                if (Insert(entity))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"SELECT
	T2.N_MDETAIL_ID AS ID,
	T3.C_PRONAME AS NAME,T2.N_PROID AS PROID
FROM
	QS_PLANMONTHAPPLY T1
INNER JOIN QS_PLANMONTH_DETAIL T2 ON T1.N_MONTHAPP_ID = T2.N_MONTHAPP_ID
INNER JOIN QS_PROJECT T3 ON T2.N_PROID = T3.N_PROID
INNER JOIN QS_PLAN_DETAILS T4 ON T1.N_MONTHAPP_ID = T4.N_PLAN_ID AND T3.C_DETAILS_CODE=T4.C_VALUE1
WHERE
	T1.N_MONTHAPP_ID={0} AND
	T4.C_VALUE1 = 'T6' AND
    T4.N_STATE=2
GROUP BY T2.N_MDETAIL_ID,T3.C_PRONAME,T2.N_PROID
ORDER BY T2.N_MDETAIL_ID";
            sql = string.Format(sql, applyId);
            var dt = Query(sql);
            foreach (DataRow row in dt.Rows)
            {
                var id = Convert.ToString(row["ID"]);
                var name = Convert.ToString(row["NAME"]);
                var pro = Convert.ToInt32(row["PROID"]);
                decimal money = 0;
                foreach (var item in data.Where(a => proId == pro))
                {
                    if (item.VALUE9 == "")
                    {
                        continue;
                    }
                    money += Convert.ToDecimal(item.VALUE9);
                }
                if (proId == pro)
                {
                    sql = "UPDATE QS_PLANMONTH_DETAIL SET N_AMOUNT=" + money + " WHERE N_MDETAIL_ID=" + id;
                    NoQuery(sql);
                }
            }
        }

        #endregion (T6)投资项目支出

        #endregion 月资金相关

        #region 年资金相关

        #region 营业收入

        public List<QS_PLAN_DETAILS1> FindRangeYear1(int applyId)
        {
            var sql = @"SELECT
            T1.C_VALUE2 AS Index,
            T1.C_VALUE3 AS Name,
            T1.C_VALUE4 AS SaleNumber,
            T1.C_VALUE5 AS UnitePrice,
            T1.C_VALUE6 AS SumPrice,
            T1.C_VALUE7 AS Flow,
            T1.C_VALUE8 AS Remark/*,
            T1.C_VALUE9 AS ParentIndex*/
            FROM QS_PLAN_DETAILS T1 WHERE T1.N_PLAN_ID=" + applyId;
            sql += " AND T1.N_STATE=1 AND T1.C_VALUE1='T1'";
            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAILS1>();
            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAILS1();
                entity.Index = Convert.ToInt32(row["INDEX"]);
                if (row["Name"] == DBNull.Value)
                {
                    entity.Name = "";
                }
                else
                {
                    entity.Name = Convert.ToString(row["Name"]);
                }
                entity.UnitePrice = Convert.ToDecimal(row["UnitePrice"]);
                entity.SaleNumber = Convert.ToInt32(row["SaleNumber"]);
                if (row["Flow"] == DBNull.Value)
                {
                    entity.Flow = "";
                }
                else
                {
                    entity.Flow = Convert.ToString(row["Flow"]);
                }
                if (row["Remark"] == DBNull.Value)
                {
                    entity.Remark = "";
                }
                else
                {
                    entity.Remark = Convert.ToString(row["Remark"]);
                }
                //if (row["ParentIndex"] == DBNull.Value)
                //{
                //    entity.ParentIndex = null;
                //}
                //else
                //{
                //    entity.ParentIndex = Convert.ToInt32(row["ParentIndex"]);
                //}
                entitys.Add(entity);
            }
            return entitys;
        }

        public void UpdateOrCreateRangeYear1(int applyId, int proId, List<QS_PLAN_DETAILS1> data)
        {
            //先清空再添加
            DeleteByApplyId(applyId, proId, 1, "T1");
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAILS();
                entity.C_VALUE1 = "T1";
                entity.C_VALUE2 = item.Index.ToString();
                entity.C_VALUE3 = item.Name;
                entity.C_VALUE4 = item.SaleNumber.ToString();
                entity.C_VALUE5 = item.UnitePrice.ToString();
                entity.C_VALUE6 = item.SumPrice.ToString();
                entity.C_VALUE7 = item.Flow.ToString();
                entity.C_VALUE8 = item.Remark.ToString();
                //if (item.ParentIndex == null)
                //{
                //    entity.C_VALUE9 = null;
                //}
                //else
                //{
                //    entity.C_VALUE9 = item.ParentIndex.ToString();
                //}
                //if (Insert(entity))
                //{
                //    count++;
                //}
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"SELECT
	T2.N_MDETAIL_ID AS ID,
    T3.C_PRONAME AS NAME
FROM
	QX_PLANYEARAPPLY T1
INNER JOIN QX_PLANYEAR_DETAIL T2 ON T1.N_MONTHAPP_ID = T2.N_MONTHAPP_ID AND T1.N_MONTHAPP_ID={0}
INNER JOIN QS_PROJECT T3 ON T2.N_PROID = T3.N_PROID
INNER JOIN QS_PLAN_DETAILS T4 ON T1.N_MONTHAPP_ID = T4.N_PLAN_ID AND T4.C_VALUE1='T1'";
            sql = string.Format(sql, applyId);
            var dt = Query(sql);
            foreach (DataRow row in dt.Rows)
            {
                var id = Convert.ToString(row["ID"]);
                var name = Convert.ToString(row["NAME"]);
                decimal money = 0;
                var ientity = data.SingleOrDefault(a => a.Name == name);// a.ParentIndex == null &&
                if (ientity != null)
                {
                    money = ientity.SumPrice;
                }
                sql = "UPDATE QX_PLANYEAR_DETAIL SET N_AMOUNT=" + money + " WHERE N_MDETAIL_ID=" + id;
                NoQuery(sql);
            }
        }

        #endregion 营业收入

        #region 预付气款

        public List<QS_PLAN_DETAILS2> FindRangeYear2(int applyId)
        {
            var sql = @"SELECT
            T1.C_VALUE2 AS C_VALUE1,
            T1.C_VALUE3 AS C_VALUE2,
            T1.C_VALUE4 AS C_VALUE3,
            T1.C_VALUE5 AS C_VALUE4,
            T1.C_VALUE6 AS C_VALUE5,
            T1.C_VALUE7 AS C_VALUE6,
            T1.C_VALUE8 AS C_VALUE7
            FROM QS_PLAN_DETAILS T1 WHERE T1.N_PLAN_ID=" + applyId;
            sql += " AND T1.N_STATE=1 AND T1.C_VALUE1='T2'";
            var dt = Query(sql);
            var entitys = new List<QS_PLAN_DETAILS2>();

            #region entity

            foreach (System.Data.DataRow row in dt.Rows)
            {
                var entity = new QS_PLAN_DETAILS2();
                if (row["C_VALUE1"] == DBNull.Value)
                {
                    entity.VALUE1 = null;
                }
                else
                {
                    entity.VALUE1 = row["C_VALUE1"].ToString();
                }
                if (row["C_VALUE2"] == DBNull.Value)
                {
                    entity.VALUE2 = null;
                }
                else
                {
                    entity.VALUE2 = row["C_VALUE2"].ToString();
                }
                if (row["C_VALUE3"] == DBNull.Value)
                {
                    entity.VALUE3 = null;
                }
                else
                {
                    entity.VALUE3 = Convert.ToInt32(row["C_VALUE3"].ToString());
                }
                if (row["C_VALUE4"] == DBNull.Value)
                {
                    entity.VALUE4 = null;
                }
                else
                {
                    entity.VALUE4 = Convert.ToDecimal(row["C_VALUE4"].ToString());
                }
                if (row["C_VALUE5"] == DBNull.Value)
                {
                    entity.VALUE5 = 0;
                }
                else
                {
                    entity.VALUE5 = Convert.ToDecimal(row["C_VALUE5"].ToString());
                }

                if (row["C_VALUE6"] == DBNull.Value)
                {
                    entity.VALUE6 = null;
                }
                else
                {
                    entity.VALUE6 = row["C_VALUE6"].ToString();
                }
                if (row["C_VALUE7"] == DBNull.Value)
                {
                    entity.VALUE7 = null;
                }
                else
                {
                    entity.VALUE7 = row["C_VALUE7"].ToString();
                }
                entitys.Add(entity);
            }

            #endregion entity

            //foreach (System.Data.DataRow row in dt.Rows)
            //{
            //    var entity = new QS_PLAN_DETAILS2();
            //    entity.VALUE1 = "T2";
            //    entity.VALUE2 = row["VALUE2"].ToString();
            //    entity.VALUE3 = Convert.ToInt32(row["VALUE3"].ToString());
            //    entity.VALUE4 = Convert.ToDecimal(row["VALUE4"].ToString());
            //    entity.VALUE5 = Convert.ToDecimal(row["VALUE5"].ToString());
            //    entity.VALUE6 = row["VALUE6"].ToString();
            //    entity.VALUE7 = row["VALUE7"].ToString();
            //    entitys.Add(entity);
            //}
            return entitys;
        }

        public void UpdateOrCreateRangeYear2(int applyId, List<QS_PLAN_DETAILS2> data)
        {
            //先清空再添加
            DeleteByApplyId(applyId, 11, 1, "T2");
            int count = 0;
            foreach (var item in data)
            {
                var entity = new QS_PLAN_DETAILS();
                entity.C_VALUE1 = "T2";
                entity.C_VALUE2 = item.VALUE1;
                entity.C_VALUE3 = item.VALUE2;
                entity.C_VALUE4 = item.VALUE3.ToString();
                entity.C_VALUE5 = item.VALUE4.ToString();
                entity.C_VALUE6 = item.VALUE5.ToString();
                entity.C_VALUE7 = item.VALUE6;
                entity.C_VALUE8 = item.VALUE7;
                entity.N_PLAN_ID = applyId;
                entity.N_STATE = 1;

                if (Insert(entity))
                {
                    count++;
                }
            }
            //更新Apply月详情表
            //查询关联的详情表
            var sql = @"SELECT T2.N_YDETAIL_ID AS ID, T3.C_PRONAME AS NAME
  FROM QX_PLANYEARAPPLY T1
 INNER JOIN QX_PLANYEAR_DETAIL T2
    ON T1.N_YEARAPP_ID = T2.N_YEARAPP_ID
   AND T1.N_YEARAPP_ID = {0}
 INNER JOIN QS_PROJECT T3
    ON T2.N_PROID = T3.N_PROID
 INNER JOIN QS_PLAN_DETAILS T4
    ON T1.N_YEARAPP_ID = T4.N_PLAN_ID
   AND T3.C_DETAILS_CODE = T4.C_VALUE1
   AND T4.N_STATE = 1
   AND T4.C_VALUE1 = 'T2'";
            sql = string.Format(sql, applyId);
            var dt = Query(sql);
            foreach (DataRow row in dt.Rows)
            {
                var id = Convert.ToString(row["ID"]);
                var name = Convert.ToString(row["NAME"]);
                decimal money = 0;
                foreach (var item in data)
                {
                    if (item.VALUE5.ToString() == "")
                    {
                        continue;
                    }
                    money += Convert.ToDecimal(item.VALUE5);
                }
                sql = "UPDATE QX_PLANYEAR_DETAIL SET N_AMOUNT=" + money + " WHERE N_YDETAIL_ID=" + id;
                NoQuery(sql);
            }
            //var id = Convert.ToString(dt.Rows[0]["ID"]);
            //sql = "UPDATE QX_PLANYEAR_DETAIL SET N_AMOUNT=" + money + " WHERE N_MDETAIL_ID=" + id;
            //NoQuery(sql);
        }

        #endregion 预付气款

        #endregion 年资金相关
    }
}

namespace Model
{
    /// <summary>
    /// 营业收入 附表1
    /// </summary>
    public class QS_PLAN_DETAILS1
    {
        /// <summary>
        /// 序号
        /// </summary>
        public int Index { get; set; }

        /// <summary>
        /// 项目名称
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 销售量
        /// </summary>
        public int? SaleNumber { get; set; }

        /// <summary>
        /// 单价
        /// </summary>
        public decimal? UnitePrice { get; set; }

        /// <summary>
        /// 总金额
        /// </summary>
        public decimal SumPrice { get; set; }

        /// <summary>
        /// 银行现金流向
        /// </summary>
        public string Flow { get; set; }

        /// <summary>
        /// 备注
        /// </summary>
        public string Remark { get; set; }

        /// <summary>
        /// 上一级Index
        /// </summary>
        //public int? ParentIndex { get; set; }
    }

    /// <summary>
    /// 预付气款 附表2
    /// </summary>
    public class QS_PLAN_DETAILS2
    {
        /// <summary>
        /// 单位名称
        /// </summary>
        public string VALUE1 { get; set; }

        /// <summary>
        /// 用户类别
        /// </summary>
        public string VALUE2 { get; set; }

        /// <summary>
        /// 预计用气量
        /// </summary>
        public int? VALUE3 { get; set; }

        /// <summary>
        /// 单价
        /// </summary>
        public decimal? VALUE4 { get; set; }

        /// <summary>
        /// 预计气款金额
        /// </summary>
        public decimal VALUE5 { get; set; }

        /// <summary>
        /// 银行现金流向
        /// </summary>
        public string VALUE6 { get; set; }

        /// <summary>
        /// 备注
        /// </summary>
        public string VALUE7 { get; set; }
    }

    /// <summary>
    /// 城市管网项目明细表 附表6
    /// </summary>
    public class QS_PLAN_DETAILS3
    {
        /// <summary>
        /// 序号
        /// </summary>
        public string VALUE1 { get; set; }

        /// <summary>
        /// 单位名称
        /// </summary>
        public string VALUE2 { get; set; }

        /// <summary>
        /// 款项类别
        /// </summary>
        public string VALUE3 { get; set; }

        /// <summary>
        /// 项目名称
        /// </summary>
        public string VALUE4 { get; set; }

        /// <summary>
        /// 款项用途
        /// </summary>
        public string VALUE5 { get; set; }

        /// <summary>
        /// 挂账金额（不含质保金）
        /// </summary>
        public string VALUE6 { get; set; }

        /// <summary>
        /// 质保金
        /// </summary>
        public string VALUE7 { get; set; }

        /// <summary>
        /// 上月止累计支付金额(不显示)
        /// </summary>
        public string VALUE8 { get; set; }

        /// <summary>
        /// 本月上报资金计划
        /// </summary>
        public string VALUE9 { get; set; }

        /// <summary>
        /// 各单位审批金额(不显示)
        /// </summary>
        public string VALUE10 { get; set; }

        /// <summary>
        /// 总部资金审批金额(不显示)
        /// </summary>
        public string VALUE11 { get; set; }

        /// <summary>
        /// 银行现金流向
        /// </summary>
        public string VALUE12 { get; set; }

        /// <summary>
        /// 备注
        /// </summary>
        public string VALUE13 { get; set; }
    }
}